import json

from flask import Blueprint, request, current_app
from utilsapp.mysql_con import db_connect, db_list, db_fetchone
from utilsapp.common import assign_value, parsing_data_id, parsing_permissions
from utilsapp.utils import aes_encrypt, ok

staic_url = "/static"


def construct_blueprint_index(*arg):
    index = Blueprint("index", __name__)

    def get_filter(reg):
        if reg.get("type") == "group":
            return f" group_id={reg.get('id')}"
        if reg.get("type") == "company":
            return f" company_id={reg.get('id')}"
        if reg.get("type") == "factory":
            return f" factory_id={reg.get('id')}"
        if reg.get("type") == "zone":
            return f" zone_id={reg.get('id')}"
        return "1=1"

    def set_query(scoped, flag):
        if scoped=="":
            return ""
        if flag == "":
            return f" and {scoped}"
        else:
            return f" and {flag}.{scoped}"

    @index.route("/stats", methods=["post", "get"])
    def a():
        scoped = parsing_data_id(0, request)
        query = ""
        if scoped != "":
            query = f" and {scoped}"
        id = assign_value(request.json, "id")
        if id != "":
            query = f" and factory_id = {id}"
        qall = f"""SELECT count(*) as warn_total from (
SELECT MAX(warn_level) as warn_level,device_id from warn where notice in (0,1) and is_show =1 {query} GROUP BY device_id ) a"""
        q1 = f"""SELECT count(*) as level_1 from (
SELECT MAX(warn_level) as warn_level,device_id from warn where notice in (0,1) and is_show =1 {query} GROUP BY device_id ) a where warn_level =1"""
        q2 = f"""SELECT count(*) as level_2 from (
SELECT MAX(warn_level) as warn_level,device_id from warn where notice in (0,1,2) and is_show =1 {query} GROUP BY device_id ) a where warn_level =2"""
        q3 = f"""SELECT count(*) as level_3 from (
SELECT MAX(warn_level) as warn_level,device_id from warn where notice in (0,1,2) and is_show =1 {query} GROUP BY device_id ) a where warn_level =3"""
        #         nall = f"""SELECT count(*) as order_total from (
        # SELECT MAX(warn_level) as warn_level,device_id from warn where notice in (1,2,4) and is_show =1 {query} GROUP BY device_id
        # ) a"""
        nall = f"""
            		SELECT count(*) as order_total
    from notice n
    JOIN userinfo u1 on u1.id = n.user_id
    JOIN userinfo u2 on u2.id = n.accept_id
    JOIN device d on n.device_id = d.id where 1=1  {set_query(scoped,'d')}
        """
        #         n1 = f"""SELECT count(*) as handled from (
        # SELECT MAX(warn_level) as warn_level,device_id from warn where notice in (2,4) and is_show =1 {query} GROUP BY device_id
        # ) a"""
        n1 = f"""
            		SELECT count(*) as handled
    from notice n
    JOIN userinfo u1 on u1.id = n.user_id
    JOIN userinfo u2 on u2.id = n.accept_id
    JOIN device d on n.device_id = d.id where 1=1 and   n.status = 3  {set_query(scoped,'d')}
        """
        n2 = f"""
            		SELECT count(*) as unHandled
    from notice n
    JOIN userinfo u1 on u1.id = n.user_id
    JOIN userinfo u2 on u2.id = n.accept_id
    JOIN device d on n.device_id = d.id where 1=1 and  n.status = 2  {set_query(scoped,'d')}
        """
        sql = f"""	SELECT * from 
	    ({qall}) a,
        ({q1}) b,
        ({q2}) c,
        ({q3}) d,
	    ( {nall}) e,
	    ( {n1}) f,
	    ({n2}) g,
	    ( SELECT count(factory_id) as factory_num from (select distinct factory_id from device where 1=1 {query}) a) h,
	    (SELECT count(id) as device_num from device where 1 =1  {query} ) i,
	    (SELECT count(id) as sensor_num from sensor where 1 =1  {query} ) l
	    """

        return db_list(sql)

    @index.route("/get/mqtt")
    def get_mqtt():
        mqtt = current_app.config["settings"]["mqtt"]
        s = json.dumps(
            {
                "url": mqtt["client-url"],
                "username": mqtt["username"],
                "password": mqtt["password"],
                "warn_topic": mqtt["warn_topic"],
                "clean": True,
                "clientId": "abc2325",
                "connectTimeout": 40000,
                "reconnectPeriod": 1000,
            }
        )
        return {"code": 0, "data": aes_encrypt(s)}

    @index.route("/get/mqtt1")
    def get_mqtt1():
        mqtt = current_app.config["settings"]["mqtt"]
        s = json.dumps(
            {
                "url": mqtt["client-url"],
                "username": mqtt["username"],
                "password": mqtt["password"],
                "warn_topic": mqtt["warn_topic"],
                "clean": True,
                "clientId": "abc2326",
                "connectTimeout": 40000,
                "reconnectPeriod": 1000,
            }
        )
        return {"code": 0, "data": s}

    @index.route("/device/distribution")
    def a1():
        scoped = parsing_data_id(0, request)
        query = ""
        if scoped != "":
            query = f" and {scoped}"
        sql = f"""SELECT r.name,a.num as value from regionlevel r 
        RIGHT JOIN (SELECT count(id) as num,factory_id from device where 1=1 {query} GROUP BY factory_id ) as a
        on r.id = a.factory_id
        where r.type = 'factory'"""
        return db_list(sql)

    @index.route("/device/data")
    def c():
        query1 = ""
        scoped = parsing_data_id(0, request)

        if "zone" in scoped:
            id = scoped.split("=")[1]
            sql = f"SELECT parent from regionlevel where id = {id}"
            res1 = db_fetchone(sql)["data"]
            scoped = f"factory_id={res1}"
        if scoped != "":
            query1 = f" and {scoped}"
        sql = f"""SELECT r.factory_id as id,c.id as city_id ,c.cityName as city,c.latitude as city_latitude,c.longitude as city_longitude,r.name as factory_name,IFNULL(d.device_num,0) as device_num,
        IFNULL(w.warn_num,0) as warn_num,s.sensor_num,IFNULL(n.order_num,0) as order_num
        from dismap c
        inner JOIN (SELECT * from (SELECT r2.id as factory_id,r2.parent as company_id,r2.name,r2.city_id ,r1.parent as group_id from regionlevel r2
LEFT JOIN regionlevel r1 on r1.id = r2.parent
where r2.type = 'factory') a where 1=1 {query1}) r on r.city_id = c.id
        LEFT JOIN ( SELECT count(id) as device_num,factory_id from device where 1=1  {query1} GROUP BY factory_id) d on d.factory_id = r.factory_id
        LEFT JOIN (SELECT count(id) as warn_num ,factory_id from warn where is_show=1  {query1} GROUP BY factory_id) w on w.factory_id = r.factory_id
        LEFT JOIN (SELECT count(id) as sensor_num,factory_id from sensor where 1=1 {query1} GROUP BY factory_id) s on s.factory_id = r.factory_id
        LEFT JOIN (SELECT count(id) as order_num ,factory_id from notice where 1 =1 {query1} GROUP BY factory_id) n on n.factory_id = r.factory_id"""

        return db_list(sql)

    @index.route("/sensor/distribution")
    def a15():
        scoped = parsing_data_id(0, request)
        query = ""
        if scoped != "":
            query = f" and {scoped}"
        sql = f"""SELECT r.name,a.num as value from regionlevel r 
            RIGHT JOIN (SELECT count(id) as num,factory_id from sensor where 1=1 {query} GROUP BY factory_id ) as a
            on r.id = a.factory_id
            where r.type = 'factory'"""
        return db_list(sql)

    # 显示近1年的告警与工单记录
    @index.route("/newdata")
    def newdata():
        scoped = parsing_data_id(0, request)
        query = ""
        if scoped != "":
            query = f" and {scoped}"
        sql = f"""
        SELECT mon.month AS time, IFNULL(w.count, 0) AS warn_num, IFNULL(n.count, 0) AS order_num
FROM (
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL a.a + (10 * b.a) + (100 * c.a) MONTH), '%Y-%m') AS month
    FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    WHERE (DATE_SUB(CURDATE(), INTERVAL a.a + (10 * b.a) + (100 * c.a) MONTH)) >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 YEAR), '%Y-%m')
) AS mon
LEFT JOIN (
    SELECT DATE_FORMAT(create_time, '%Y-%m') AS month, COUNT(*) AS count
    FROM warn
    WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND is_show = 1 {query}
    GROUP BY month
) AS w ON w.month = mon.month
LEFT JOIN (
    SELECT DATE_FORMAT(end_time, '%Y-%m') AS month, COUNT(*) AS count
    FROM notice
    WHERE end_time >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)  {query}
    GROUP BY month
) AS n ON n.month = mon.month
WHERE mon.month BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 YEAR), '%Y-%m') AND DATE_FORMAT(CURDATE(), '%Y-%m')
ORDER BY mon.month DESC;
"""
        return db_list(sql)

    @index.route("/getConfig", methods=["post"])
    def ssd():
        data = current_app.config["settings"]
        return ok(data)

    # 最新告警,显示最近的5条
    @index.route("/recent/alarm", methods=["post"])
    def recent_alarm():
        scoped = parsing_data_id(0, request)
        query = ""
        if scoped != "":
            query = f" and w.{scoped}"
        id = assign_value(request.json, "id")
        if id != "":
            query += f" and w.factory_id = {id}"
        sql = f"""SELECT w.id,w.sensor_id,w.device_id,d.device_name,w.title,w.content,w.warn_level ,w.create_time
	    from (SELECT * from warn where is_show=1 and notice not in (3,4)) w 
        LEFT JOIN device d on w.device_id = d.id where 1=1 {query} ORDER BY w.create_time desc LIMIT 5 """
        return db_list(sql)

    # 查询工厂下区域信息
    @index.route("/zones", methods=["post"])
    def get_zoneinfo():
        id = assign_value(request.json, "id")
        if id != "":
            sql = f"""SELECT r1.id as zone_id,r1.name as zone_name,r1.x_per,r1.y_per,r2.id as factory_id,r2.name as factory_name,
        r3.id as company_id,r3.name as company_name,r4.id as group_id,r4.name as group_name,IFNULL(w.warn_num,0) as warn_num,
        ifnull(n.order_num,0) as order_num,ifnull(d.device_num,0) as device_num
        FROM (SELECT * from regionlevel where type='zone' and parent = {id}) r1
        LEFT JOIN  (SELECT * from regionlevel where type='factory') r2 on r1.parent = r2.id
        LEFT JOIN  (SELECT * from regionlevel where type='company') r3 on r2.parent = r3.id
        LEFT JOIN (SELECT * from regionlevel where type='group') r4 on r3.parent = r4.id
        LEFT JOIN (SELECT count(id) as warn_num,zone_id from warn where is_show=1 GROUP BY zone_id) w on w.zone_id = r1.id
        LEFT JOIN (SELECT count(id) as order_num,zone_id from notice where 1=1 GROUP BY zone_id) n on n.zone_id = r1.id
        LEFT JOIN (SELECT count(id) as device_num,zone_id from device where 1=1 GROUP BY zone_id) d on d.zone_id = r1.id
        """
        res = db_list(sql)
        sql1 = f"SELECT concat('{staic_url}',factory_img) as factory_img from regionlevel where id = {id}"
        factory_img = db_list(sql1)["data"][0]
        res.update(factory_img)
        return res

    return index
